﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Drawing;
using System.Drawing.Imaging;
using System.Globalization;
using System.Linq;
using System.Text;
using BusinessObjects;
using Interfaces;
using Toolkit;

namespace DataAccess {
  /// <summary>
  /// Data Access Layer for children
  /// </summary>
  public class ChildrenDal : IChildrenLogic {

    private TrustPersonDal trustPersonDal = new TrustPersonDal( );

    #region - Public methods -

    /// <summary>
    /// Gets the children.
    /// </summary>
    /// <param name="allChildren">if set to <c>true</c> [all children].</param>
    /// <returns></returns>
    public Children GetChildren( bool allChildren ) {
      Children children = GetBasicChildData( allChildren );

      return children;
    }

    /// <summary>
    /// Gets the child.
    /// </summary>
    /// <param name="childId">The child id.</param>
    /// <returns></returns>
    public Child GetChild( int childId ) {
      Child child = null;
      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteCommand command = CreateSelectChildCommand( connection, childId ) ) {
          try {
            using ( SQLiteDataReader reader = command.ExecuteReader( CommandBehavior.CloseConnection ) ) {
              while ( reader.Read( ) ) {
                child = FillChildFromReader( reader );
              }
            }
          } catch ( Exception ex ) {
            throw new Exception( ex.Message, ex );
          }
        }
      }
      return child;
    }

    /// <summary>
    /// Saves the children.
    /// </summary>
    /// <param name="children">The children.</param>
    public void SaveChildren( Children children ) {
      foreach ( Child child in children ) {
        SaveChild( child );
      }
    }

    /// <summary>
    /// Saves the child.
    /// </summary>
    /// <param name="child">The child.</param>
    public void SaveChild( Child child ) {
      if ( child.IsNew ) {
        InsertChild( child );
      } else {
        if ( child.HasChanged ) {
          UpdateChild( child );
        }
      }

    }

    /// <summary>
    /// Deletes the specified child.
    /// </summary>
    /// <param name="child">The child.</param>
    public void Delete( Child child ) {
      if ( child.Link != 0 ) throw new DataException( "Child nog gekoppeld." );

      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteTransaction transaction = connection.BeginTransaction( ) ) {
          using ( SQLiteCommand command = CreateDeleteCommand( connection, child.Id ) ) {
            command.Transaction = transaction;
            try {
              command.ExecuteNonQuery( );
              transaction.Commit( );
            } catch ( Exception ex ) {
              transaction.Rollback( );
              throw new DataException( ex.Message, ex );
            } finally {
              if ( connection.State != ConnectionState.Closed ) {
                connection.Close( );
              }
            }
          }
        }
      }


    }

    /// <summary>
    /// Gets the linked children.
    /// </summary>
    /// <param name="linkId">The link id.</param>
    /// <returns></returns>
    public Children GetLinkedChildren( int linkId ) {
      Children children = new Children( );
      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteCommand command = CreateSelectLinkedCommand( connection, linkId ) ) {
          try {
            using ( SQLiteDataReader reader = command.ExecuteReader( CommandBehavior.CloseConnection ) ) {
              while ( reader.Read( ) ) {
                Child child = FillChildFromReader( reader );
                children.Add( child );
              }
            }
          } catch ( Exception ex ) {
            throw new DataException( ex.Message, ex );
          }
        }
      }
      return children;
    }

    #endregion

    #region - Private methods -

    private void UpdateChild( Child child ) {
      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteTransaction transaction = connection.BeginTransaction( ) ) {
          SQLiteCommand command = new SQLiteCommand( );

          command.Transaction = transaction;
          try {

            if ( child.Mother.Employer.Id == 0 ) {
              InsertEmployer( child.Mother.Employer, connection, command );
            } else {
              command = CreateUpdateParentsEmployerCommand( connection, child.Mother.Employer );
              command.ExecuteNonQuery( );
            }

            if ( child.Father.Employer.Id == 0 ) {
              InsertEmployer( child.Father.Employer, connection, command );
            } else {
              command = CreateUpdateParentsEmployerCommand( connection, child.Father.Employer );
              command.ExecuteNonQuery( );
            }

            if ( child.Mother.Id == 0 ) {
              InsertParent( child.Mother, connection, command, "Mothers" );
            } else {
              command = CreateUpdateMotherCommand( connection, child );
              command.ExecuteNonQuery( );
            }

            if ( child.Father.Id == 0 ) {
              InsertParent( child.Father, connection, command, "Fathers" );
            } else {
              command = CreateUpdateFatherCommand( connection, child );
              command.ExecuteNonQuery( );
            }

            command = CreateUpdateScheduleCommand( connection, child );
            command.ExecuteNonQuery( );

            command = CreateUpdateChildrenCommand( connection, child );
            command.ExecuteNonQuery( );

            transaction.Commit( );
            child.MarkClean( );
          } catch ( Exception ex ) {
            transaction.Rollback( );
            throw new DataException( ex.Message, ex );
          } finally {
            if ( connection.State != System.Data.ConnectionState.Closed ) {
              connection.Close( );
            }
          }
        }
      }
    }

    private void InsertChild( Child child ) {

      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteTransaction transaction = connection.BeginTransaction( ) ) {
          SQLiteCommand command = new SQLiteCommand( );
          command.Transaction = transaction;

          try {
            //insert mother employer
            InsertEmployer( child.Mother.Employer, connection, command );

            //insert father employer
            InsertEmployer( child.Father.Employer, connection, command );

            //insert mother
            InsertParent( child.Mother, connection, command, "Mothers" );

            //insert father
            InsertParent( child.Father, connection, command, "Fathers" );

            //insert child
            if ( !string.IsNullOrEmpty( child.Name ) ) {
              command = CreateInsertChildCommand( connection, child );
              command.ExecuteNonQuery( );

              command = CreateSelectAfterInsertCommand( connection, "Children" );
              child.Id = Convert.ToInt32( command.ExecuteScalar( ), CultureInfo.CurrentCulture );
            }

            //insert schedule for child
            command = CreateInsertScheduleCommand( connection, child );
            command.ExecuteNonQuery( );

            command = CreateSelectAfterInsertCommand( connection, "Schedule" );
            child.Schedule.Id = Convert.ToInt32( command.ExecuteScalar( ), CultureInfo.CurrentCulture );

            transaction.Commit( );
            child.MarkClean( );

          } catch ( Exception ex ) {
            transaction.Rollback( );
            throw new DataException( ex.Message, ex );
          } finally {
            if ( connection.State != ConnectionState.Closed ) {
              connection.Close( );
            }
          }

        }
      }

    }

    private SQLiteCommand InsertParent( Parent parent, SQLiteConnection connection, SQLiteCommand command, string tableName ) {
      if ( !string.IsNullOrEmpty( parent.Name ) ) {

        command = tableName == "Mothers" ? CreateInsertMotherCommand( connection, parent ):CreateInsertFatherCommand(connection,parent);
        command.ExecuteNonQuery( );

        command = CreateSelectAfterInsertCommand( connection, tableName );
        parent.Id = Convert.ToInt32( command.ExecuteScalar( ), CultureInfo.CurrentCulture );
      }
      return command;
    }

    private SQLiteCommand InsertEmployer( ParentEmployer employer, SQLiteConnection connection, SQLiteCommand command ) {
      if ( !string.IsNullOrEmpty( employer.Name ) ) {
        command = CreateInsertParentEmployerCommand( connection, employer );
        command.ExecuteNonQuery( );

        command = CreateSelectAfterInsertCommand( connection, "ParentsEmployers" );
        employer.Id = Convert.ToInt32( command.ExecuteScalar( ), CultureInfo.CurrentCulture );
      }
      return command;
    }

    private Children GetBasicChildData( bool allChildren ) {
      Children children = new Children( );

      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteCommand command = CreateSelectAllCommand( connection, allChildren ) ) {
          try {
            using ( SQLiteDataReader reader = command.ExecuteReader( CommandBehavior.CloseConnection ) ) {

              while ( reader.Read( ) ) {
                Child child = FillChildFromReader( reader );
                child.MarkClean( );
                children.Add( child );
              }
            }
          } catch ( SQLiteException ex ) {
            throw new SQLiteException( ex.Message, ex );
          } catch ( Exception ex ) {
            throw new DataException( ex.Message );

          } finally {
            if ( connection.State != System.Data.ConnectionState.Closed ) {
              connection.Close( );
            }
          }
        }
      }

      return children;
    }

    private Child FillChildFromReader( SQLiteDataReader reader ) {
      int id = reader.GetInt32( reader.GetOrdinal( "ChildId" ) );
      string name = reader.GetValue( reader.GetOrdinal( "ChildName" ) ).ToString( );
      Child child = new Child( id, name );
      child.FirstName = reader.GetValue( reader.GetOrdinal( "ChildFirstName" ) ).ToString( );
      child.NickName = reader.GetValue( reader.GetOrdinal( "ChildNickName" ) ).ToString( );
      child.Birthday = reader.GetValue( reader.GetOrdinal( "ChildBirthday" ) ) == DBNull.Value ? null : ( DateTime? ) reader.GetDateTime( reader.GetOrdinal( "ChildBirthday" ) );
      child.DateIn = reader.GetValue( reader.GetOrdinal( "ChildDateIn" ) ) == DBNull.Value ? null : ( DateTime? ) reader.GetValue( reader.GetOrdinal( "ChildDateIn" ) );
      child.DateOut = reader.GetValue( reader.GetOrdinal( "ChildDateOut" ) ) == DBNull.Value ? null : ( DateTime? ) reader.GetValue( reader.GetOrdinal( "ChildDateOut" ) );
      child.MaxDays = reader.GetByte( reader.GetOrdinal( "ChildMaxDays" ) );
      child.MinDays = reader.GetByte( reader.GetOrdinal( "ChildMinDays" ) );
      child.MaxTime = reader.GetValue( reader.GetOrdinal( "ChildMaxTime" ) ).ToString( );
      child.MinTime = reader.GetValue( reader.GetOrdinal( "ChildMinTime" ) ).ToString( );

      DateTime? arrivalDate = reader.GetValue( reader.GetOrdinal( "ArrivalDateTime" ) ) == DBNull.Value ? null : ( DateTime? ) reader.GetValue( reader.GetOrdinal( "ArrivalDateTime" ) );
      DateTime? departureDate = reader.GetValue( reader.GetOrdinal( "DepartureDateTime" ) ) == DBNull.Value ? null : ( DateTime? ) reader.GetValue( reader.GetOrdinal( "DepartureDateTime" ) );

      string arrivalTime = arrivalDate.HasValue ? arrivalDate.Value.ToShortTimeString( ).PadLeft( 5, '0' ) : "00:00";
      string departureTime = departureDate.HasValue ? departureDate.Value.ToShortTimeString( ).PadLeft( 5, '0' ) : "00:00";
      bool arrivalTimeEmpty = ( string.IsNullOrEmpty( arrivalTime ) || string.IsNullOrEmpty( arrivalTime.Trim( ) ) || arrivalTime.Trim( ) == ":" || arrivalTime == "00:00" );
      bool departureTimeEmpty = ( string.IsNullOrEmpty( departureTime ) || string.IsNullOrEmpty( departureTime.Trim( ) ) || departureTime.Trim( ) == ":" || departureTime == "00:00" );

      child.ArrivalDateTime = arrivalDate;
      child.DepartureDateTime = departureDate;

      if ( reader.GetValue( reader.GetOrdinal( "ChildPhoto" ) ) != DBNull.Value ) {
        byte[ ] photo = ( byte[ ] ) reader.GetValue( reader.GetOrdinal( "ChildPhoto" ) );
        Image image = GraphicTools.Convert.ByteArrayToImage( photo );
        if ( image != null ) {
          child.Photo = image;
        }
      }
      child.Link = reader.GetValue( reader.GetOrdinal( "ChildLink" ) ) == DBNull.Value ? 0 : reader.GetInt32( reader.GetOrdinal( "ChildLink" ) );
      child.Remarks = reader.GetValue( reader.GetOrdinal( "ChildRemarks" ) ).ToString( );

      child.Mother.Id = reader.GetInt32( reader.GetOrdinal( "MotherId" ) );
      child.Mother.Name = reader.GetValue( reader.GetOrdinal( "MotherName" ) ).ToString( );
      child.Mother.FirstName = reader.GetValue( reader.GetOrdinal( "MotherFirstName" ) ).ToString( );
      child.Mother.Phone = reader.GetValue( reader.GetOrdinal( "MotherPhone" ) ).ToString( );
      child.Mother.Gsm = reader.GetValue( reader.GetOrdinal( "MotherGsm" ) ).ToString( );
      child.Mother.Email = reader.GetValue( reader.GetOrdinal( "MotherEmail" ) ).ToString( );
      child.Mother.NationalNumber = reader.GetValue( reader.GetOrdinal( "MotherNationalNumber" ) ).ToString( );
      child.Mother.MainAddress = reader.GetValue( reader.GetOrdinal( "MotherMainAddress" ) ) == DBNull.Value ? false : ( bool ) reader.GetValue( reader.GetOrdinal( "MotherMainAddress" ) );
      child.Mother.BasicPrice = reader.GetValue( reader.GetOrdinal( "BasicPrice" ) ) == DBNull.Value ? 0m : Convert.ToDecimal( reader.GetValue( reader.GetOrdinal( "BasicPrice" ) ), CultureInfo.CurrentCulture );
      if ( reader.GetValue( reader.GetOrdinal( "MotherPhoto" ) ) != DBNull.Value ) {
        byte[ ] photo = ( byte[ ] ) reader.GetValue( reader.GetOrdinal( "MotherPhoto" ) );
        Image image = GraphicTools.Convert.ByteArrayToImage( photo );
        if ( image != null ) {
          child.Mother.Photo = image;
        }
      }

      child.Mother.Address.Street = reader.GetValue( reader.GetOrdinal( "MotherStreet" ) ).ToString( );
      child.Mother.Address.Zipcode = reader.GetValue( reader.GetOrdinal( "MotherZipcode" ) ).ToString( );
      child.Mother.Address.ZipcodeId = reader.GetValue( reader.GetOrdinal( "MotherZipcodeId" ) ) == DBNull.Value ? 0 : reader.GetInt32( reader.GetOrdinal( "MotherZipcodeId" ) );
      child.Mother.Address.City = reader.GetValue( reader.GetOrdinal( "MotherCity" ) ).ToString( );
      child.Mother.Address.CountryName = reader.GetValue( reader.GetOrdinal( "MotherCountry" ) ).ToString( );
      child.Mother.Address.CountryCode = reader.GetValue( reader.GetOrdinal( "MotherCountryCode" ) ).ToString( );
      child.Mother.Address.CountryId = reader.GetValue( reader.GetOrdinal( "MotherCountryId" ) ) == DBNull.Value ? 0 : reader.GetInt32( reader.GetOrdinal( "MotherCountryId" ) );

      if ( reader.GetValue( reader.GetOrdinal( "MotherEmployerId" ) ) != DBNull.Value ) {
        child.Mother.Employer.Id = reader.GetInt32( reader.GetOrdinal( "MotherEmployerId" ) );
        child.Mother.Employer.Name = reader.GetValue( reader.GetOrdinal( "MotherEmployerName" ) ).ToString( );
        child.Mother.Employer.Address.Street = reader.GetValue( reader.GetOrdinal( "MotherEmployerStreet" ) ).ToString( );
        child.Mother.Employer.Address.City = reader.GetValue( reader.GetOrdinal( "MotherEmployerCity" ) ).ToString( );
        child.Mother.Employer.Address.Zipcode = reader.GetValue( reader.GetOrdinal( "MotherEmployerZipcode" ) ).ToString( );
        child.Mother.Employer.Address.ZipcodeId = reader.GetValue( reader.GetOrdinal( "MotherEmployerZipcodeId" ) ) == DBNull.Value ? 0 : reader.GetInt32( reader.GetOrdinal( "MotherEmployerZipcodeId" ) );
        child.Mother.Employer.Address.CountryName = reader.GetValue( reader.GetOrdinal( "MotherEmployerCountry" ) ).ToString( );
        child.Mother.Employer.Address.CountryCode = reader.GetValue( reader.GetOrdinal( "MotherEmployerCountryCode" ) ).ToString( );
        child.Mother.Employer.Address.CountryId = reader.GetValue( reader.GetOrdinal( "MotherEmployerCountryId" ) ) == DBNull.Value ? 0 : reader.GetInt32( reader.GetOrdinal( "MotherEmployerCountryId" ) );
      }


      if ( reader.GetValue( reader.GetOrdinal( "FatherId" ) ) != DBNull.Value ) {
        child.Father.Id = reader.GetInt32( reader.GetOrdinal( "FatherId" ) );
        child.Father.Name = reader.GetValue( reader.GetOrdinal( "FatherName" ) ).ToString( );
        child.Father.FirstName = reader.GetValue( reader.GetOrdinal( "FatherFirstName" ) ).ToString( );
        child.Father.Phone = reader.GetValue( reader.GetOrdinal( "FatherPhone" ) ).ToString( );
        child.Father.Gsm = reader.GetValue( reader.GetOrdinal( "FatherGsm" ) ).ToString( );
        child.Father.Email = reader.GetValue( reader.GetOrdinal( "FatherEmail" ) ).ToString( );
        child.Father.NationalNumber = reader.GetValue( reader.GetOrdinal( "FatherNationalNumber" ) ).ToString( );
        child.Father.MainAddress = reader.GetValue( reader.GetOrdinal( "FatherMainAddress" ) ) == DBNull.Value ? false : ( bool ) reader.GetValue( reader.GetOrdinal( "FatherMainAddress" ) );
        if ( reader.GetValue( reader.GetOrdinal( "FatherPhoto" ) ) != DBNull.Value ) {
          byte[ ] photo = ( byte[ ] ) reader.GetValue( reader.GetOrdinal( "FatherPhoto" ) );
          Image image = GraphicTools.Convert.ByteArrayToImage( photo );
          if ( image != null ) {
            child.Father.Photo = image;
          }
        }
        child.Father.Address.Street = reader.GetValue( reader.GetOrdinal( "FatherStreet" ) ).ToString( );
        child.Father.Address.Zipcode = reader.GetValue( reader.GetOrdinal( "FatherZipcode" ) ).ToString( );
        child.Father.Address.ZipcodeId = reader.GetValue( reader.GetOrdinal( "FatherZipcodeId" ) ) == DBNull.Value ? 0 : reader.GetInt32( reader.GetOrdinal( "FatherZipcodeId" ) );
        child.Father.Address.City = reader.GetValue( reader.GetOrdinal( "FatherCity" ) ).ToString( );
        child.Father.Address.CountryName = reader.GetValue( reader.GetOrdinal( "FatherCountry" ) ).ToString( );
        child.Father.Address.CountryCode = reader.GetValue( reader.GetOrdinal( "FatherCountryCode" ) ).ToString( );
        child.Father.Address.CountryId = reader.GetValue( reader.GetOrdinal( "FatherCountryId" ) ) == DBNull.Value ? 0 : reader.GetInt32( reader.GetOrdinal( "FatherCountryId" ) );

        if ( reader.GetValue( reader.GetOrdinal( "FatherEmployerId" ) ) != DBNull.Value ) {
          child.Father.Employer.Id = reader.GetInt32( reader.GetOrdinal( "FatherEmployerId" ) );
          child.Father.Employer.Name = reader.GetValue( reader.GetOrdinal( "FatherEmployerName" ) ).ToString( );
          child.Father.Employer.Phone = reader.GetValue( reader.GetOrdinal( "FatherEmployerPhone" ) ).ToString( );
          child.Father.Employer.Address.Street = reader.GetValue( reader.GetOrdinal( "FatherEmployerStreet" ) ).ToString( );
          child.Father.Employer.Address.ZipcodeId = reader.GetValue( reader.GetOrdinal( "FatherEmployerZipcodeId" ) ) == DBNull.Value ? 0 : reader.GetInt32( reader.GetOrdinal( "FatherEmployerZipcodeId" ) );
          child.Father.Employer.Address.Zipcode = reader.GetValue( reader.GetOrdinal( "FatherEmployerZipcode" ) ).ToString( );
          child.Father.Employer.Address.City = reader.GetValue( reader.GetOrdinal( "FatherEmployerCity" ) ).ToString( );
          child.Father.Employer.Address.CountryName = reader.GetValue( reader.GetOrdinal( "FatherEmployerCountry" ) ).ToString( );
          child.Father.Employer.Address.CountryCode = reader.GetValue( reader.GetOrdinal( "FatherEmployerCountryCode" ) ).ToString( );
          child.Father.Employer.Address.CountryId = reader.GetValue( reader.GetOrdinal( "FatherEmployerCountryId" ) ) == DBNull.Value ? 0 : reader.GetInt32( reader.GetOrdinal( "FatherEmployerCountryId" ) );
        }
      }
      child.Schedule.Id = reader.GetInt32( reader.GetOrdinal( "ScheduleId" ) );
      child.Schedule.Type = ( Schedule.ScheduleType ) reader.GetValue( reader.GetOrdinal( "ScheduleType" ) );
      child.Schedule.Week1MoFrom = reader.GetValue( reader.GetOrdinal( "Week1MoFrom" ) ).ToString( );
      child.Schedule.Week1MoTo = reader.GetValue( reader.GetOrdinal( "Week1MoTo" ) ).ToString( );
      child.Schedule.Week1TuFrom = reader.GetValue( reader.GetOrdinal( "Week1TuFrom" ) ).ToString( );
      child.Schedule.Week1TuTo = reader.GetValue( reader.GetOrdinal( "Week1TuTo" ) ).ToString( );
      child.Schedule.Week1WeFrom = reader.GetValue( reader.GetOrdinal( "Week1WeFrom" ) ).ToString( );
      child.Schedule.Week1WeTo = reader.GetValue( reader.GetOrdinal( "Week1WeTo" ) ).ToString( );
      child.Schedule.Week1WeFrom = reader.GetValue( reader.GetOrdinal( "Week1WeFrom" ) ).ToString( );
      child.Schedule.Week1ThTo = reader.GetValue( reader.GetOrdinal( "Week1ThTo" ) ).ToString( );
      child.Schedule.Week1ThFrom = reader.GetValue( reader.GetOrdinal( "Week1ThFrom" ) ).ToString( );
      child.Schedule.Week1FrTo = reader.GetValue( reader.GetOrdinal( "Week1FrTo" ) ).ToString( );
      child.Schedule.Week1FrFrom = reader.GetValue( reader.GetOrdinal( "Week1FrFrom" ) ).ToString( );

      child.Schedule.Week2MoFrom = reader.GetValue( reader.GetOrdinal( "Week2MoFrom" ) ).ToString( );
      child.Schedule.Week2MoTo = reader.GetValue( reader.GetOrdinal( "Week2MoTo" ) ).ToString( );
      child.Schedule.Week2TuFrom = reader.GetValue( reader.GetOrdinal( "Week2TuFrom" ) ).ToString( );
      child.Schedule.Week2TuTo = reader.GetValue( reader.GetOrdinal( "Week2TuTo" ) ).ToString( );
      child.Schedule.Week2WeFrom = reader.GetValue( reader.GetOrdinal( "Week2WeFrom" ) ).ToString( );
      child.Schedule.Week2WeTo = reader.GetValue( reader.GetOrdinal( "Week2WeTo" ) ).ToString( );
      child.Schedule.Week2WeFrom = reader.GetValue( reader.GetOrdinal( "Week2WeFrom" ) ).ToString( );
      child.Schedule.Week2ThTo = reader.GetValue( reader.GetOrdinal( "Week2ThTo" ) ).ToString( );
      child.Schedule.Week2ThFrom = reader.GetValue( reader.GetOrdinal( "Week2ThFrom" ) ).ToString( );
      child.Schedule.Week2FrTo = reader.GetValue( reader.GetOrdinal( "Week2FrTo" ) ).ToString( );
      child.Schedule.Week2FrFrom = reader.GetValue( reader.GetOrdinal( "Week2FrFrom" ) ).ToString( );

      child.Schedule.Week3MoFrom = reader.GetValue( reader.GetOrdinal( "Week3MoFrom" ) ).ToString( );
      child.Schedule.Week3MoTo = reader.GetValue( reader.GetOrdinal( "Week3MoTo" ) ).ToString( );
      child.Schedule.Week3TuFrom = reader.GetValue( reader.GetOrdinal( "Week3TuFrom" ) ).ToString( );
      child.Schedule.Week3TuTo = reader.GetValue( reader.GetOrdinal( "Week3TuTo" ) ).ToString( );
      child.Schedule.Week3WeFrom = reader.GetValue( reader.GetOrdinal( "Week3WeFrom" ) ).ToString( );
      child.Schedule.Week3WeTo = reader.GetValue( reader.GetOrdinal( "Week3WeTo" ) ).ToString( );
      child.Schedule.Week3WeFrom = reader.GetValue( reader.GetOrdinal( "Week3WeFrom" ) ).ToString( );
      child.Schedule.Week3ThTo = reader.GetValue( reader.GetOrdinal( "Week3ThTo" ) ).ToString( );
      child.Schedule.Week3ThFrom = reader.GetValue( reader.GetOrdinal( "Week3ThFrom" ) ).ToString( );
      child.Schedule.Week3FrTo = reader.GetValue( reader.GetOrdinal( "Week3FrTo" ) ).ToString( );
      child.Schedule.Week3FrFrom = reader.GetValue( reader.GetOrdinal( "Week3FrFrom" ) ).ToString( );

      child.Schedule.Week4MoFrom = reader.GetValue( reader.GetOrdinal( "Week4MoFrom" ) ).ToString( );
      child.Schedule.Week4MoTo = reader.GetValue( reader.GetOrdinal( "Week4MoTo" ) ).ToString( );
      child.Schedule.Week4TuFrom = reader.GetValue( reader.GetOrdinal( "Week4TuFrom" ) ).ToString( );
      child.Schedule.Week4TuTo = reader.GetValue( reader.GetOrdinal( "Week4TuTo" ) ).ToString( );
      child.Schedule.Week4WeFrom = reader.GetValue( reader.GetOrdinal( "Week4WeFrom" ) ).ToString( );
      child.Schedule.Week4WeTo = reader.GetValue( reader.GetOrdinal( "Week4WeTo" ) ).ToString( );
      child.Schedule.Week4WeFrom = reader.GetValue( reader.GetOrdinal( "Week4WeFrom" ) ).ToString( );
      child.Schedule.Week4ThTo = reader.GetValue( reader.GetOrdinal( "Week4ThTo" ) ).ToString( );
      child.Schedule.Week4ThFrom = reader.GetValue( reader.GetOrdinal( "Week4ThFrom" ) ).ToString( );
      child.Schedule.Week4FrTo = reader.GetValue( reader.GetOrdinal( "Week4FrTo" ) ).ToString( );
      child.Schedule.Week4FrFrom = reader.GetValue( reader.GetOrdinal( "Week4FrFrom" ) ).ToString( );

      child.TrustPersons = trustPersonDal.GetTrustPersonsForChild( child.Id );
      return child;
    }

    #region - Commands for select -

    private SQLiteCommand CreateSelectAllCommand( SQLiteConnection connection, bool allChildren ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = GetBasicSelect( );
      command.Parameters.AddWithValue( "@date1", DateTime.Now.Date );
      command.Parameters.AddWithValue( "@date2", DateTime.Now.Date.AddDays( 1 ) );
      if ( !allChildren ) {
        command.CommandText += " WHERE Children.[State] = 0";
      }
      return command;
    }

    private SQLiteCommand CreateSelectChildCommand( SQLiteConnection connection, int childId ) {
      SQLiteCommand command = connection.CreateCommand( );
      DateTime date1 = DateTime.Now.Date;
      DateTime date2 = date1.AddDays( 1 );
      command.CommandText = GetBasicSelect( );
      command.CommandText += " WHERE Children.ID = @id";

      command.Parameters.AddWithValue( "@date1", date1 );
      command.Parameters.AddWithValue( "@date2", date2 );
      command.Parameters.AddWithValue( "@id", childId );

      return command;
    }

    private SQLiteCommand CreateSelectLinkedCommand( SQLiteConnection connection, int linkId ) {
      SQLiteCommand command = connection.CreateCommand( );
      DateTime date1 = DateTime.Now.Date;
      DateTime date2 = date1.AddDays( 1 );
      command.CommandText = GetBasicSelect( )
        + " WHERE Children.ChildLinkId = @linkid";
      command.Parameters.AddWithValue( "@date1", date1 );
      command.Parameters.AddWithValue( "@date2", date2 );
      command.Parameters.AddWithValue( "@linkid", linkId );
      return command;
    }

    #endregion

    #region - Commands for update -

    private SQLiteCommand CreateUpdateParentsEmployerCommand( SQLiteConnection connection, ParentEmployer employer ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"Update ParentsEmployers  
                              SET Name = @name, Street = @street, ZipcodeId = @zipcodeid, Phone = @phone
                              WHERE ID = @id";
      command.Parameters.AddWithValue( "@name", employer.Name );
      command.Parameters.AddWithValue( "@street", employer.Address.Street );
      command.Parameters.AddWithValue( "@zipcodeid", employer.Address.ZipcodeId );
      command.Parameters.AddWithValue( "@phone", employer.Phone );
      command.Parameters.AddWithValue( "@id", employer.Id );
      return command;
    }

    private SQLiteCommand CreateUpdateChildrenCommand( SQLiteConnection connection, Child child ) {
      SQLiteCommand command = connection.CreateCommand( );

      command.CommandText = @"UPDATE Children 
                              SET Name = @name, FirstName = @firstname, 
                              NickName = @nickname, DateIn = @datein, 
                              DateOut = @dateout, Birthday = @birthday, 
                              MinDaysPerMonth = @mindays, MaxDaysPerMonth = @maxdays,
                              MinStartTime = @mintime, MaxStopTime = @maxtime, 
                              State = @state, ActiveState = @activestate,
                              Photo = @photo, Remarks = @remarks,
                              ChildLinkId = @childlinkid, MotherId = @motherid,
                              FatherId = @fatherid
                              WHERE ID = @id";
      command.Parameters.AddWithValue( "@name", child.Name );
      command.Parameters.AddWithValue( "@firstname", child.FirstName );
      command.Parameters.AddWithValue( "@nickname", child.NickName );
      command.Parameters.AddWithValue( "@datein", child.DateIn );
      command.Parameters.AddWithValue( "@dateout", child.DateOut );
      command.Parameters.AddWithValue( "@birthday", child.Birthday );
      command.Parameters.AddWithValue( "@mindays", child.MinDays );
      command.Parameters.AddWithValue( "@maxdays", child.MaxDays );
      command.Parameters.AddWithValue( "@mintime", child.MinTime );
      command.Parameters.AddWithValue( "@maxtime", child.MaxTime );
      command.Parameters.AddWithValue( "@state", ( byte ) child.ChildState );
      command.Parameters.AddWithValue( "@activestate", ( byte ) child.ChildActiveState );
      if ( child.Photo != null ) {
        byte[ ] photo = GraphicTools.Convert.ImageToByteArray( child.Photo, child.Photo.RawFormat );
        command.Parameters.AddWithValue( "@photo", photo );
      } else {
        command.Parameters.AddWithValue( "@photo", null );
      }
      command.Parameters.AddWithValue( "@remarks", child.Remarks );
      command.Parameters.AddWithValue( "@childlinkid", child.Link );
      command.Parameters.AddWithValue( "@motherid", child.Mother.Id );
      command.Parameters.AddWithValue( "@fatherid", child.Father.Id );
      command.Parameters.AddWithValue( "@id", child.Id );

      return command;
    }

    private SQLiteCommand CreateUpdateMotherCommand( SQLiteConnection connection, Child child ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"UPDATE Mothers
                              SET Name = @name, FirstName = @firstname,
                              Phone = @phone, GSM = @gsm, Email = @email,
                              NationalNumber = @nationalnumber, Photo = @photo,
                              Street = @street, ZipcodeId = @zipcodeid,
                              EmployerId = @employerid, MainAddress = @mainaddress,
                              BasicPrice = @basicprice 
                              WHERE ID = @id";
      command.Parameters.AddWithValue( "@name", child.Mother.Name );
      command.Parameters.AddWithValue( "@firstname", child.Mother.FirstName );
      command.Parameters.AddWithValue( "@phone", child.Mother.Phone );
      command.Parameters.AddWithValue( "@gsm", child.Mother.Gsm );
      command.Parameters.AddWithValue( "@email", child.Mother.Email );
      command.Parameters.AddWithValue( "@nationalnumber", child.Mother.NationalNumber );
      if ( child.Mother.Photo != null ) {
        byte[ ] photo = GraphicTools.Convert.ImageToByteArray( child.Mother.Photo, child.Mother.Photo.RawFormat );
        command.Parameters.AddWithValue( "@photo", photo );
      } else {
        command.Parameters.AddWithValue( "@photo", null );
      }
      command.Parameters.AddWithValue( "@street", child.Mother.Address.Street );
      command.Parameters.AddWithValue( "@zipcodeid", child.Mother.Address.ZipcodeId );
      command.Parameters.AddWithValue( "@employerid", child.Mother.Employer.Id );
      command.Parameters.AddWithValue( "@mainaddress", child.Mother.MainAddress );
      command.Parameters.AddWithValue( "@basicprice", child.Mother.BasicPrice );
      command.Parameters.AddWithValue( "@id", child.Mother.Id );

      return command;
    }

    private SQLiteCommand CreateUpdateFatherCommand( SQLiteConnection connection, Child child ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"UPDATE Fathers
                              SET Name = @name, FirstName = @firstname,
                              Phone = @phone, GSM = @gsm, Email = @email,
                              NationalNumber = @nationalnumber, Photo = @photo,
                              Street = @street, ZipcodeId = @zipcodeid,
                              EmployerId = @employerid, MainAddress = @mainaddress
                              WHERE ID = @id";
      command.Parameters.AddWithValue( "@name", child.Father.Name );
      command.Parameters.AddWithValue( "@firstname", child.Father.FirstName );
      command.Parameters.AddWithValue( "@phone", child.Father.Phone );
      command.Parameters.AddWithValue( "@gsm", child.Father.Gsm );
      command.Parameters.AddWithValue( "@email", child.Father.Email );
      command.Parameters.AddWithValue( "@nationalnumber", child.Father.NationalNumber );
      if ( child.Father.Photo != null ) {
        byte[ ] photo = GraphicTools.Convert.ImageToByteArray( child.Father.Photo, child.Father.Photo.RawFormat );
        command.Parameters.AddWithValue( "@photo", photo );
      } else {
        command.Parameters.AddWithValue( "@photo", null );
      }
      command.Parameters.AddWithValue( "@street", child.Father.Address.Street );
      command.Parameters.AddWithValue( "@zipcodeid", child.Father.Address.ZipcodeId );
      command.Parameters.AddWithValue( "@employerid", child.Father.Employer.Id );
      command.Parameters.AddWithValue( "@mainaddress", child.Father.MainAddress );
      command.Parameters.AddWithValue( "@id", child.Father.Id );

      return command;
    }

    private SQLiteCommand CreateUpdateScheduleCommand( SQLiteConnection connection, Child child ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"UPDATE Schedule
                              SET ChildId = @childid, ScheduleType = @scheduletype,
                              Week1MoFrom = @week1mofrom, Week1MoTo = @week1moto,
                              Week1TuFrom = @week1tufrom, Week1TuTo = @week1tuto,
                              Week1WeFrom = @week1wefrom, Week1WeTo = @week1weto,
                              Week1ThFrom = @week1thfrom, Week1ThTo = @week1thto,
                              Week1FrFrom = @week1frfrom, Week1FrTo = @week1frto,
                              Week2MoFrom = @week2mofrom, Week2MoTo = @week2moto,
                              Week2TuFrom = @week2tufrom, Week2TuTo = @week2tuto,
                              Week2WeFrom = @week2wefrom, Week2WeTo = @week2weto,
                              Week2ThFrom = @week2thfrom, Week2ThTo = @week2thto,
                              Week2FrFrom = @week2frfrom, Week2FrTo = @week2frto,
                              Week3MoFrom = @week3mofrom, Week3MoTo = @week3moto,
                              Week3TuFrom = @week3tufrom, Week3TuTo = @week3tuto,
                              Week3WeFrom = @week3wefrom, Week3WeTo = @week3weto,
                              Week3ThFrom = @week3thfrom, Week3ThTo = @week3thto,
                              Week3FrFrom = @week3frfrom, Week3FrTo = @week3frto,
                              Week4MoFrom = @week4mofrom, Week4MoTo = @week4moto,
                              Week4TuFrom = @week4tufrom, Week4TuTo = @week4tuto,
                              Week4WeFrom = @week4wefrom, Week4WeTo = @week4weto,
                              Week4ThFrom = @week4thfrom, Week4ThTo = @week4thto,
                              Week4FrFrom = @week4frfrom, Week4FrTo = @week4frto
                              WHERE ID = @id";
      command.Parameters.AddWithValue( "@childid", child.Id );
      command.Parameters.AddWithValue( "@scheduletype", ( byte ) child.Schedule.Type );
      command.Parameters.AddWithValue( "@week1mofrom", child.Schedule.Week1MoFrom );
      command.Parameters.AddWithValue( "@week1moto", child.Schedule.Week1MoTo );
      command.Parameters.AddWithValue( "@week1tufrom", child.Schedule.Week1TuFrom );
      command.Parameters.AddWithValue( "@week1tuto", child.Schedule.Week1TuTo );
      command.Parameters.AddWithValue( "@week1wefrom", child.Schedule.Week1WeFrom );
      command.Parameters.AddWithValue( "@week1weto", child.Schedule.Week1WeTo );
      command.Parameters.AddWithValue( "@week1thfrom", child.Schedule.Week1ThFrom );
      command.Parameters.AddWithValue( "@week1thto", child.Schedule.Week1ThTo );
      command.Parameters.AddWithValue( "@week1frfrom", child.Schedule.Week1FrFrom );
      command.Parameters.AddWithValue( "@week1frto", child.Schedule.Week1FrTo );
      command.Parameters.AddWithValue( "@week2mofrom", child.Schedule.Week2MoFrom );
      command.Parameters.AddWithValue( "@week2moto", child.Schedule.Week2MoTo );
      command.Parameters.AddWithValue( "@week2tufrom", child.Schedule.Week2TuFrom );
      command.Parameters.AddWithValue( "@week2tuto", child.Schedule.Week2TuTo );
      command.Parameters.AddWithValue( "@week2wefrom", child.Schedule.Week2WeFrom );
      command.Parameters.AddWithValue( "@week2weto", child.Schedule.Week2WeTo );
      command.Parameters.AddWithValue( "@week2thfrom", child.Schedule.Week2ThFrom );
      command.Parameters.AddWithValue( "@week2thto", child.Schedule.Week2ThTo );
      command.Parameters.AddWithValue( "@week2frfrom", child.Schedule.Week2FrFrom );
      command.Parameters.AddWithValue( "@week2frto", child.Schedule.Week2FrTo );
      command.Parameters.AddWithValue( "@week3mofrom", child.Schedule.Week3MoFrom );
      command.Parameters.AddWithValue( "@week3moto", child.Schedule.Week3MoTo );
      command.Parameters.AddWithValue( "@week3tufrom", child.Schedule.Week3TuFrom );
      command.Parameters.AddWithValue( "@week3tuto", child.Schedule.Week3TuTo );
      command.Parameters.AddWithValue( "@week3wefrom", child.Schedule.Week3WeFrom );
      command.Parameters.AddWithValue( "@week3weto", child.Schedule.Week3WeTo );
      command.Parameters.AddWithValue( "@week3thfrom", child.Schedule.Week3ThFrom );
      command.Parameters.AddWithValue( "@week3thto", child.Schedule.Week3ThTo );
      command.Parameters.AddWithValue( "@week3frfrom", child.Schedule.Week3FrFrom );
      command.Parameters.AddWithValue( "@week3frto", child.Schedule.Week3FrTo );
      command.Parameters.AddWithValue( "@week4mofrom", child.Schedule.Week4MoFrom );
      command.Parameters.AddWithValue( "@week4moto", child.Schedule.Week4MoTo );
      command.Parameters.AddWithValue( "@week4tufrom", child.Schedule.Week4TuFrom );
      command.Parameters.AddWithValue( "@week4tuto", child.Schedule.Week4TuTo );
      command.Parameters.AddWithValue( "@week4wefrom", child.Schedule.Week4WeFrom );
      command.Parameters.AddWithValue( "@week4weto", child.Schedule.Week4WeTo );
      command.Parameters.AddWithValue( "@week4thfrom", child.Schedule.Week4ThFrom );
      command.Parameters.AddWithValue( "@week4thto", child.Schedule.Week4ThTo );
      command.Parameters.AddWithValue( "@week4frfrom", child.Schedule.Week4FrFrom );
      command.Parameters.AddWithValue( "@week4frto", child.Schedule.Week4FrTo );
      command.Parameters.AddWithValue( "@id", child.Schedule.Id );
      return command;
    }

    #endregion

    #region - Commands for insert -

    private SQLiteCommand CreateInsertParentEmployerCommand( SQLiteConnection connection, ParentEmployer employer ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"INSERT INTO ParentsEmployers (Name, Street, ZipcodeId, Phone)
                              VALUES (@name, @street, @zipcodeid, @phone)";
      command.Parameters.AddWithValue( "@name", employer.Name );
      command.Parameters.AddWithValue( "@street", employer.Address.Street );
      command.Parameters.AddWithValue( "@zipcodeid", employer.Address.ZipcodeId );
      command.Parameters.AddWithValue( "@phone", employer.Phone );

      return command;
    }

    private SQLiteCommand CreateInsertMotherCommand( SQLiteConnection connection, Parent parent ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"INSERT INTO Mothers (Name, FirstName, Phone, GSM, Email,
                              NationalNumber, Photo, Street, ZipcodeId, EmployerId, MainAddress, BasicPrice)
                              VALUES (@name, @firstname, @phone, @gsm, @email, @nationalnumber,
                              @photo, @street, @zipcodeid, @employerid, @mainaddress, @basicprice)";
      command.Parameters.AddWithValue( "@name", parent.Name );
      command.Parameters.AddWithValue( "@firstname", parent.FirstName );
      command.Parameters.AddWithValue( "@phone", parent.Phone );
      command.Parameters.AddWithValue( "@gsm", parent.Gsm );
      command.Parameters.AddWithValue( "@email", parent.Email );
      command.Parameters.AddWithValue( "@nationalnumber", parent.NationalNumber );
      byte[ ] photo = null;
      if ( parent.Photo != null ) {
        photo = GraphicTools.Convert.ImageToByteArray( parent.Photo, parent.Photo.RawFormat );
      }
      command.Parameters.AddWithValue( "@photo", photo );
      command.Parameters.AddWithValue( "@street", parent.Address.Street );
      command.Parameters.AddWithValue( "@zipcodeid", parent.Address.ZipcodeId );
      command.Parameters.AddWithValue( "@employerid", parent.Employer.Id );
      command.Parameters.AddWithValue( "@mainaddress", parent.MainAddress );
      command.Parameters.AddWithValue( "@basicprice", parent.BasicPrice );
      return command;
    }

    private SQLiteCommand CreateInsertFatherCommand( SQLiteConnection connection, Parent parent ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"INSERT INTO Fathers (Name, FirstName, Phone, GSM, Email,
                              NationalNumber, Photo, Street, ZipcodeId, EmployerId, MainAddress)
                              VALUES (@name, @firstname, @phone, @gsm, @email, @nationalnumber,
                              @photo, @street, @zipcodeid, @employerid, @mainaddress)";
      command.Parameters.AddWithValue( "@name", parent.Name );
      command.Parameters.AddWithValue( "@firstname", parent.FirstName );
      command.Parameters.AddWithValue( "@phone", parent.Phone );
      command.Parameters.AddWithValue( "@gsm", parent.Gsm );
      command.Parameters.AddWithValue( "@email", parent.Email );
      command.Parameters.AddWithValue( "@nationalnumber", parent.NationalNumber );
      byte[ ] photo = null;
      if ( parent.Photo != null ) {
        photo = GraphicTools.Convert.ImageToByteArray( parent.Photo, parent.Photo.RawFormat );
      }
      command.Parameters.AddWithValue( "@photo", photo );
      command.Parameters.AddWithValue( "@street", parent.Address.Street );
      command.Parameters.AddWithValue( "@zipcodeid", parent.Address.ZipcodeId );
      command.Parameters.AddWithValue( "@employerid", parent.Employer.Id );
      command.Parameters.AddWithValue( "@mainaddress", parent.MainAddress );

      return command;
    }

    private SQLiteCommand CreateInsertScheduleCommand( SQLiteConnection connection, Child child ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"INSERT INTO Schedule(ChildId, ScheduleType,
                              Week1MoFrom, Week1MoTo,  Week1TuFrom, Week1TuTo,  Week1WeFrom, Week1WeTo,
                              Week1ThFrom, Week1ThTo,  Week1FrFrom, Week1FrTo,
                              Week2MoFrom, Week2MoTo,  Week2TuFrom, Week2TuTo,  Week2WeFrom, Week2WeTo,
                              Week2ThFrom, Week2ThTo,  Week2FrFrom, Week2FrTo,
                              Week3MoFrom, Week3MoTo,  Week3TuFrom, Week3TuTo,  Week3WeFrom, Week3WeTo,
                              Week3ThFrom, Week3ThTo,  Week3FrFrom, Week3FrTo,
                              Week4MoFrom, Week4MoTo,  Week4TuFrom, Week4TuTo,  Week4WeFrom, Week4WeTo,
                              Week4ThFrom, Week4ThTo,  Week4FrFrom, Week4FrTo)
                              VALUES (@childid, @scheduletype,
                              @week1mofrom, @week1moto, @week1tufrom, @week1tuto,  @week1wefrom, @week1weto,
                              @week1thfrom, @week1thto, @week1frfrom, @week1frto,
                              @week2mofrom, @week2moto, @week2tufrom, @week2tuto,  @week2wefrom, @week2weto,
                              @week2thfrom, @week2thto, @week2frfrom, @week2frto,
                              @week3mofrom, @week3moto, @week3tufrom, @week3tuto,  @week3wefrom, @week3weto,
                              @week3thfrom, @week3thto, @week3frfrom, @week3frto,
                              @week4mofrom, @week4moto, @week4tufrom, @week4tuto,  @week4wefrom, @week4weto,
                              @week4thfrom, @week4thto, @week4frfrom, @week4frto)";
      command.Parameters.AddWithValue( "@childid", child.Id );
      command.Parameters.AddWithValue( "@scheduletype", ( byte ) child.Schedule.Type );

      command.Parameters.AddWithValue( "@week1mofrom", child.Schedule.Week1MoFrom );
      command.Parameters.AddWithValue( "@week1moto", child.Schedule.Week1MoTo );
      command.Parameters.AddWithValue( "@week1tufrom", child.Schedule.Week1TuFrom );
      command.Parameters.AddWithValue( "@week1tuto", child.Schedule.Week1TuTo );
      command.Parameters.AddWithValue( "@week1wefrom", child.Schedule.Week1WeFrom );
      command.Parameters.AddWithValue( "@week1weto", child.Schedule.Week1WeTo );
      command.Parameters.AddWithValue( "@week1thfrom", child.Schedule.Week1ThFrom );
      command.Parameters.AddWithValue( "@week1thto", child.Schedule.Week1ThTo );
      command.Parameters.AddWithValue( "@week1frfrom", child.Schedule.Week1FrFrom );
      command.Parameters.AddWithValue( "@week1frto", child.Schedule.Week1FrTo );

      command.Parameters.AddWithValue( "@week2mofrom", child.Schedule.Week2MoFrom );
      command.Parameters.AddWithValue( "@week2moto", child.Schedule.Week2MoTo );
      command.Parameters.AddWithValue( "@week2tufrom", child.Schedule.Week2TuFrom );
      command.Parameters.AddWithValue( "@week2tuto", child.Schedule.Week2TuTo );
      command.Parameters.AddWithValue( "@week2wefrom", child.Schedule.Week2WeFrom );
      command.Parameters.AddWithValue( "@week2weto", child.Schedule.Week2WeTo );
      command.Parameters.AddWithValue( "@week2thfrom", child.Schedule.Week2ThFrom );
      command.Parameters.AddWithValue( "@week2thto", child.Schedule.Week2ThTo );
      command.Parameters.AddWithValue( "@week2frfrom", child.Schedule.Week2FrFrom );
      command.Parameters.AddWithValue( "@week2frto", child.Schedule.Week2FrTo );

      command.Parameters.AddWithValue( "@week3mofrom", child.Schedule.Week3MoFrom );
      command.Parameters.AddWithValue( "@week3moto", child.Schedule.Week3MoTo );
      command.Parameters.AddWithValue( "@week3tufrom", child.Schedule.Week3TuFrom );
      command.Parameters.AddWithValue( "@week3tuto", child.Schedule.Week3TuTo );
      command.Parameters.AddWithValue( "@week3wefrom", child.Schedule.Week3WeFrom );
      command.Parameters.AddWithValue( "@week3weto", child.Schedule.Week3WeTo );
      command.Parameters.AddWithValue( "@week3thfrom", child.Schedule.Week3ThFrom );
      command.Parameters.AddWithValue( "@week3thto", child.Schedule.Week3ThTo );
      command.Parameters.AddWithValue( "@week3frfrom", child.Schedule.Week3FrFrom );
      command.Parameters.AddWithValue( "@week3frto", child.Schedule.Week3FrTo );

      command.Parameters.AddWithValue( "@week4mofrom", child.Schedule.Week4MoFrom );
      command.Parameters.AddWithValue( "@week4moto", child.Schedule.Week4MoTo );
      command.Parameters.AddWithValue( "@week4tufrom", child.Schedule.Week4TuFrom );
      command.Parameters.AddWithValue( "@week4tuto", child.Schedule.Week4TuTo );
      command.Parameters.AddWithValue( "@week4wefrom", child.Schedule.Week4WeFrom );
      command.Parameters.AddWithValue( "@week4weto", child.Schedule.Week4WeTo );
      command.Parameters.AddWithValue( "@week4thfrom", child.Schedule.Week4ThFrom );
      command.Parameters.AddWithValue( "@week4thto", child.Schedule.Week4ThTo );
      command.Parameters.AddWithValue( "@week4frfrom", child.Schedule.Week4FrFrom );
      command.Parameters.AddWithValue( "@week4frto", child.Schedule.Week4FrTo );

      return command;
    }

    private SQLiteCommand CreateInsertChildCommand( SQLiteConnection connection, Child child ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"INSERT INTO Children (Name, FirstName, NickName, DateIn, DateOut,
                              Birthday, MinDaysPerMonth, MaxDaysPerMonth, MinStartTime, MaxStopTime,
                              State, ActiveState, Photo, ChildLinkId, MotherId, FatherId, Remarks)
                              VALUES (@name, @firstname, @nickname, @datein, @dateout,
                              @birthday, @mindayspermonth, @maxdayspermonth, @minstarttime, @maxstoptime,
                              @state, @activestate, @photo, @childlinkid, @motherid, @fatherid, @remarks)";
      command.Parameters.AddWithValue( "@name", child.Name );
      command.Parameters.AddWithValue( "@firstname", child.FirstName );
      command.Parameters.AddWithValue( "@nickname", child.NickName );
      command.Parameters.AddWithValue( "@datein", child.DateIn );
      command.Parameters.AddWithValue( "@dateout", child.DateOut );
      command.Parameters.AddWithValue( "@birthday", child.Birthday );
      command.Parameters.AddWithValue( "@mindayspermonth", child.MinDays );
      command.Parameters.AddWithValue( "@maxdayspermonth", child.MaxDays );
      command.Parameters.AddWithValue( "@minstarttime", child.MinTime );
      command.Parameters.AddWithValue( "@maxstoptime", child.MaxTime );
      command.Parameters.AddWithValue( "@state", ( byte ) child.ChildState );
      command.Parameters.AddWithValue( "@activestate", ( byte ) child.ChildActiveState );
      byte[ ] photo = null;
      if ( child.Photo != null ) {
        photo = GraphicTools.Convert.ImageToByteArray( child.Photo, child.Photo.RawFormat );
      }
      command.Parameters.AddWithValue( "@photo", photo );
      command.Parameters.AddWithValue( "@childlinkid", child.Link );
      command.Parameters.AddWithValue( "@motherid", child.Mother.Id );
      command.Parameters.AddWithValue( "@fatherid", child.Father.Id );
      command.Parameters.AddWithValue( "@remarks", child.Remarks );

      return command;
    }

    private SQLiteCommand CreateSelectAfterInsertCommand( SQLiteConnection connection, string tableName ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = "SELECT ID FROM " + tableName + " WHERE ID = last_insert_rowid()";
      return command;
    }

    #endregion

    #region - Commands for delete -

    private SQLiteCommand CreateDeleteCommand( SQLiteConnection connection, int childId ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"DELETE FROM Children WHERE ID = @id";
      command.Parameters.AddWithValue( "@id", childId );
      return command;
    }

    #endregion

    private string GetBasicSelect( ) {
      return @"SELECT Children.ID AS ChildId,Children.[Name] AS ChildName, Children.FirstName AS ChildFirstName, 
       Children.[NickName] AS ChildNickName, Children.[Birthday] AS ChildBirthday,       
       Children.[DateIn] AS ChildDateIn, Children.[DateOut] AS ChildDateOut,       
       Children.[MaxDaysPerMonth] AS ChildMaxDays, Children.[MinDaysPerMonth] AS ChildMinDays,       
       Children.[MinStartTime] AS ChildMinTime, Children.[MaxStopTime] AS ChildMaxTime,       
       Children.[State] AS ChildState, Children.[ActiveState] AS ChildActiveState,
       Children.[Photo] AS ChildPhoto, Children.ChildLinkId AS ChildLink,
       Children.[Remarks] AS ChildRemarks, 
       Mothers.ID AS MotherId, Mothers.Name AS MotherName, Mothers.[FirstName] AS MotherFirstName,
       Mothers.[Phone] AS MotherPhone, Mothers.[GSM] AS MotherGsm, Mothers.[Email] AS MotherEmail,       
       Mothers.[NationalNumber] AS MotherNationalNumber, Mothers.[Photo] AS MotherPhoto, 
       Mothers.Street AS MotherStreet, Mothers.MainAddress AS MotherMainAddress,    
       Mothers.BasicPrice AS BasicPrice,          
       mZipCodes.[ID] AS MotherZipcodeId,
       mZipCodes.ZipCode AS MotherZipcode, mZipCodes.[City] AS MotherCity, 
       mCountries.Country AS MotherCountry, mCountries.[CountryCode] AS MotherCountryCode,       
       mCountries.[ID] AS MotherCountryId,
       mEmployers.Id AS MotherEmployerId,
       mEmployers.Name AS MotherEmployerName, mEmployers.[Phone] AS MotherEmployerPhone, 
       mEmployers.[Street] AS MotherEmployerStreet,        
       mempZipcodes.[ID] AS MotherEmployerZipcodeId,
       mempZipcodes.[ZipCode] AS MotherEmployerZipcode, mempZipCodes.[City] AS MotherEmployerCity,
       mempCountries.[Country] AS MotherEmployerCountry, mempCountries.[ID] AS MotherEmployerCountryId, 
       mempCountries.[CountryCode] AS MotherEmployerCountryCode,
       
       Fathers.ID AS FatherId, Fathers.Name AS FatherName, Fathers.[FirstName] AS FatherFirstName,       
       Fathers.[Phone] AS FatherPhone, Fathers.[GSM] AS FatherGsm, Fathers.[Email] AS FatherEmail,       
       Fathers.[NationalNumber] AS FatherNationalNumber, Fathers.[Photo] AS FatherPhoto,
       Fathers.Street AS FatherStreet, Fathers.MainAddress AS FatherMainAddress,
       fZipCodes.[City] AS FatherCity, fZipCodes.ZipCode AS FatherZipcode,        
       fZipCodes.[ID] AS FatherZipcodeId, fCountries.[CountryCode] AS FatherCountryCode,
       fCountries.Country AS FatherCountry, fCountries.[ID] AS FatherCountryId, 
       fEmployers.Id AS FatherEmployerId,
       fEmployers.Name AS FatherEmployerName, fEmployers.[Phone] AS FatherEmployerPhone,
       fEmployers.[Street] AS FatherEmployerStreet,
       fempZipcodes.[ZipCode] AS FatherEmployerZipcode, fempZipcodes.[ID] AS FatherEmployerZipcodeId,
       fempZipcodes.[City] AS FatherEmployerCity, fempCountries.[CountryCode] AS FatherEmployerCountryCode,
       fempCountries.[Country] AS FatherEmployerCountry, fempCountries.[ID] AS FatherEmployerCountryId,       
       Schedule.ID AS ScheduleId, Schedule.[ScheduleType] AS ScheduleType, 
       Schedule.[Week1MoFrom] AS Week1MoFrom, Schedule.[Week1MoTo] AS Week1MoTo, 
       Schedule.[Week1TuFrom] AS Week1TuFrom, Schedule.[Week1TuTo] AS Week1TuTo, 
       Schedule.[Week1WeFrom] AS Week1WeFrom, Schedule.[Week1WeTo] AS Week1WeTo, 
       Schedule.[Week1ThFrom] AS Week1ThFrom, Schedule.[Week1ThTo] AS Week1ThTo, 
       Schedule.[Week1FrFrom] AS Week1FrFrom, Schedule.[Week1FrTo] AS Week1FrTo,         
       Schedule.[Week2MoFrom] AS Week2MoFrom, Schedule.[Week2MoTo] AS Week2MoTo, 
       Schedule.[Week2TuFrom] AS Week2TuFrom, Schedule.[Week2TuTo] AS Week2TuTo, 
       Schedule.[Week2WeFrom] AS Week2WeFrom, Schedule.[Week2WeTo] AS Week2WeTo, 
       Schedule.[Week2ThFrom] AS Week2ThFrom, Schedule.[Week2ThTo] AS Week2ThTo, 
       Schedule.[Week2FrFrom] AS Week2FrFrom, Schedule.[Week2FrTo] AS Week2FrTo,  
       Schedule.[Week3MoFrom] AS Week3MoFrom, Schedule.[Week3MoTo] AS Week3MoTo, 
       Schedule.[Week3TuFrom] AS Week3TuFrom, Schedule.[Week3TuTo] AS Week3TuTo, 
       Schedule.[Week3WeFrom] AS Week3WeFrom, Schedule.[Week3WeTo] AS Week3WeTo, 
       Schedule.[Week3ThFrom] AS Week3ThFrom, Schedule.[Week3ThTo] AS Week3ThTo, 
       Schedule.[Week3FrFrom] AS Week3FrFrom, Schedule.[Week3FrTo] AS Week3FrTo,       
       Schedule.[Week4MoFrom] AS Week4MoFrom, Schedule.[Week4MoTo] AS Week4MoTo, 
       Schedule.[Week4TuFrom] AS Week4TuFrom, Schedule.[Week4TuTo] AS Week4TuTo, 
       Schedule.[Week4WeFrom] AS Week4WeFrom, Schedule.[Week4WeTo] AS Week4WeTo, 
       Schedule.[Week4ThFrom] AS Week4ThFrom, Schedule.[Week4ThTo] AS Week4ThTo, 
       Schedule.[Week4FrFrom] AS Week4FrFrom, Schedule.[Week4FrTo] AS Week4FrTo,       
       ChildrenDiaries.[ArrivalDateTime] AS ArrivalDateTime,       
       ChildrenDiaries.[DepartureDateTime] AS DepartureDateTime

FROM Children INNER JOIN Mothers ON Children.MotherId = Mothers.ID 
     LEFT JOIN Schedule ON Children.ID = Schedule.ChildId
     LEFT JOIN Fathers ON Children.FatherId = Fathers.ID 
     LEFT JOIN ZipCodes mZipCodes ON Mothers.ZipcodeId = mZipCodes.ID 
     LEFT JOIN ZipCodes fZipCodes ON Fathers.ZipcodeId = fZipCodes.ID 
     LEFT JOIN Countries mCountries ON mZipCodes.CountryId = mCountries.ID 
     LEFT JOIN Countries fCountries ON fZipCodes.CountryId = fCountries.ID     
     LEFT JOIN ParentsEmployers mEmployers ON mEmployers.[ID] = Mothers.EmployerId    
     LEFT JOIN ZipCodes mempZipcodes ON mempZipcodes.[ID] = mEmployers.ZipcodeId      
     LEFT JOIN Countries mempCountries ON mempCountries.[ID] = mempZipcodes.CountryId
     LEFT JOIN ParentsEmployers fEmployers ON fEmployers.[ID] = Fathers.EmployerId     
     LEFT JOIN ZipCodes fempZipcodes ON fempZipcodes.[ID]=fEmployers.ZipcodeId     
     LEFT JOIN Countries fempCountries ON fempCountries.[ID] = fempZipcodes.CountryId     
     LEFT OUTER JOIN ChildrenDiaries on Children.ID = ChildrenDiaries.ChildId AND ChildrenDiaries.[ArrivalDateTime] between @date1 and @date2";
    }

    #endregion

  }
}
